#!/usr/bin/env python

import psycopg2
import sys
import os
import time
from mutagen.easyid3 import EasyID3
from mutagen.mp3 import MP3

'''
Script walks a directory structure starting at the ROOT_PATH value. 

Logic is as follows 

    walk through dirpath, dirnames, dirfiles under ROOT_PATH
        loop for each file in dirfiles
            read in ID3 tags
            insert new track entry to db if not exist
                insert new track artist entry to db if not exist
            insert new album entry to db if not exist
                insert new artist if not exist
                insert new album path if not exist
                insert new genere if not exist
            insert new album_map entry if not exist
'''

ROOT_PATH = "/mnt/media/Audio/"

try:
    conn = psycopg2.connect("dbname=mp3 user=mp3 host=192.168.0.1 password=mp3")
    cur = conn.cursor()
except Exception:
    print "Error %d: %s" % (e.args[0], e.args[1])
    sys.exit (1)


def curr_val(seq):
    """
    returns current sequence value
    """
    cur.execute("SELECT currval(%s)", (seq,)) 
    return cur.fetchone()[0]

def agenre (gre, create = True):
    s = """SELECT genre_id 
             FROM genre
            WHERE name = %s"""

    cur.execute(s, (gre, ))
    row = cur.fetchone()

    if cur.rowcount == 0 and create:
        s = """INSERT INTO genre
                         (name)
             VALUES
                         (%s)"""

        cur.execute(s, (gre,))
        return curr_val('seq_genre_id')
    else:
        print "Genre exists : %s" % gre
        return row[0]

def track (aname, name, file, create = True):

    aid = artist(aname)

    s = """SELECT track_id
           FROM tracks
           WHERE title = %s
             AND artist_id = %s"""
    cur.execute(s , (name, aid))
    row = cur.fetchone()
    
    if cur.rowcount == 0 and create:
        s = """INSERT INTO tracks 
                        (artist_id, title, file_name) 
                VALUES
                        (%s, %s, %s)"""
        cur.execute(s , (aid, name, file))
        return curr_val('seq_track_id')
    else:
        print "track exists : %s " % file
        return row[0]
    
def artist (name, create = True):

    s = """SELECT artist_id 
             FROM artist 
            WHERE name = %s""" 
    cur.execute(s , (name,))
    row = cur.fetchone()

    if cur.rowcount == 0 and create:
        s = """INSERT INTO artist 
                        (name) 
                 VALUES
                        (%s) """
        cur.execute(s , (name,))
        return curr_val('seq_artist_id')
    else:
        print "artist exists : %s " % name
        return row[0]

def apath (path, create = True):

    s = """SELECT path_id
             FROM path
            WHERE name = %s"""
    cur.execute(s , (path,))
    row = cur.fetchone()

    if cur.rowcount == 0 and create:
        s = """INSERT INTO path
                      (name, create_date)
               VALUES
                      (%s, %s)"""
        cur.execute(s , (path, get_modify_time(path)))
        return curr_val('seq_path_id')
    else:
        return row[0]

def album (art, name, path, year, gre, create = True):
    
    s = """SELECT abm.album_id
             FROM album abm , path pth
            WHERE abm.name = %s
              AND pth.name = %s
              AND pth.path_id = abm.path_id"""
    cur.execute(s , (name, path))
    row = cur.fetchone()

    if cur.rowcount == 0 and create:
        s = """INSERT INTO album
                      (artist_id, name, path_id, year, genre_id)
               VALUES
                      (%s, %s, %s, %s, %s)"""
        aid = artist(art)
        pid = apath(path)
        gid = agenre(gre)

        cur.execute(s , (aid, name, pid, year, gid))
        return curr_val('seq_album_id')
    else:
        print "album exists : %s - %s " % (art, name)
        return row[0]
    
def album_map (alb_id, tr_id, create = True):

    s = """SELECT track_id, album_id
           FROM album_map
          WHERE track_id = %s
            AND album_id = %s"""
    cur.execute (s , (tr_id, alb_id))

    if cur.rowcount == 0 and create:
        s = """INSERT INTO album_map
                    (album_id, track_id)
             VALUES
                    (%s, %s)"""
        cur.execute(s , (alb_id, tr_id))

def get_modify_time(p):
    """
    Returns the last modified time of the supplied path
    """
    # http://developer.postgresql.org/pgdocs/postgres/runtime-config-client.html#GUC-DATESTYLE
    # mp3=> show datestyle;
    # DateStyle 
    #-----------
    # ISO, DMY
    #(1 row)
    return time.strftime('%d-%m-%Y', time.gmtime(os.path.getmtime(p)))

## main ##

print "Walking directory, hold tight..."

for path, dirs, files in os.walk(ROOT_PATH):
    for file in files:
        if not file.lower().endswith('mp3'):
            continue
        try:
            a = MP3(os.path.join(path, file), ID3=EasyID3)
            tname = a["title"][0]
            aname = a["artist"][0]
            albname = a["album"][0]
        except:
            print "WARN: unable to load ID3 tags : %s" % (file)
            continue

        try:
            genre = a["genre"][0]
        except KeyError:
            genre = "Unknown"

        try:
            year = a["date"][0][:4]
        except KeyError:
            year = '1900'
    
        if year == '':
            year = '1900'
        
        try:
            comp = a["composer"][0]
        except KeyError:
            comp = aname

        tr = track(aname, tname, file)
        alb = album(comp, albname, path, year, genre)
        album_map(alb, tr)
        conn.commit()

cur.close()
conn.close()
